package com.xlongwei.accounts.dao;

import java.util.Collections;
import java.util.List;
import java.util.Map;
import org.noear.solon.Utils;
import org.noear.solon.annotation.Component;
import org.noear.solon.annotation.Inject;
import org.noear.solon.data.annotation.Tran;
import org.noear.solon.data.sql.SqlBuilder;
import org.smartboot.http.common.utils.CollectionUtils;
import com.xlongwei.accounts.config.DsConfig;
import com.xlongwei.accounts.config.MySqlUtils;
import com.xlongwei.accounts.model.Record;
import com.xlongwei.accounts.view.Query;
import com.xlongwei.accounts.view.Report;

@Component
public class RecordDao {
    @Inject
    MySqlUtils sqlUtils;

    @Tran
    public Integer create(Record record) {
        assert record.getRecordid() == null;
        Object key = sqlUtils.sql(
                "insert into t_record(`day`,amount,ledgerid,accountid_in,accountid_out,memberid,userid) values(?,?,?,?,?,?,?)",
                record.getDay(), record.getAmount(), record.getLedgerid(), record.getAccountidIn(),
                record.getAccountidOut(), record.getMemberid(), record.getUserid())
                .updateReturnKey();
        return Integer.parseInt(key.toString());
    }

    public Record retrieve(Integer recordid) {
        return sqlUtils.sql("select recordid,`day`,amount,ledgerid,accountid_in,accountid_out,memberid,userid from t_record where recordid=?", recordid)
                .queryRow(Record.class);
    }

    @Tran
    public Integer update(Record record) {
        assert record.getRecordid() != null;
        return sqlUtils.sql(
                "update t_record set `day`=?,amount=?,ledgerid=?,accountid_in=?,accountid_out=?,memberid=? where recordid=?",
                record.getDay(), record.getAmount(), record.getLedgerid(), record.getAccountidIn(),
                record.getAccountidOut(), record.getMemberid(), record.getRecordid()).update();
    }

    @SuppressWarnings("rawtypes")
    public List<Map> report(Report report) {
        assert CollectionUtils.isNotEmpty(report.getUserids());
        SqlBuilder sqlSpec = new SqlBuilder();
        //h2不支持：if(accountid_in>0,accountid_in,accountid_out)，也不支持date_format(`day`,'%Y-%m-%d')
        sqlSpec.append("select concat(extract(YEAR from `day`),'-',lpad(extract(MONTH from `day`),2,'0')) `year_month`");
        if ("ledger".equals(report.getGroupBy())) {
            sqlSpec.append(",ledgerid");
        } else if ("member".equals(report.getGroupBy())) {
            sqlSpec.append(",memberid");
        } else if("account".equals(report.getGroupBy())) {
            sqlSpec.append(",(case when accountid_in>0 then accountid_in else accountid_out end) accountid");
        } else if ("user".equals(report.getGroupBy())) {
            sqlSpec.append(",userid");
        }
        boolean withTransfer = report.getLedgerids()!=null && report.getLedgerids().size()==1 && report.getLedgerids().get(0)==3;
        if (report.isCountRecords()) { // RecordService按100分转换为1元，因此每条流水计为100（转换一下就是1条流水），分转元的逻辑就不用改动
            sqlSpec.append(",sum(case when accountid_in>0 and accountid_out=0 then 100 else 0 end) sum_in,sum(case when accountid_out>0 and accountid_in=0 then 100 else 0 end) sum_out,sum(case when accountid_out>0 and accountid_in>0 then 100 else 0 end) transfers");
        } else {
            sqlSpec.append(",sum(case when accountid_in>0 then amount else 0 end) sum_in,sum(case when accountid_out>0 then amount else 0 end) sum_out");
        }
        if(CollectionUtils.isNotEmpty(report.getAccountids()) && withTransfer) {
            sqlSpec.append(" from ("+ //出入报表需指定账户，并将转账拆分为收入和支出两条，从而按收支报表来计算，分组、家庭、成员的逻辑可以复用
            "select recordid,`day`,amount,ledgerid,accountid_in,accountid_out,memberid,userid from t_record where userid in (?...) and ((accountid_in=0 and accountid_out in (?...)) or (accountid_out=0 and accountid_in in (?...)))"+
            " union select recordid,`day`,amount,ledgerid,accountid_in,0 as accountid_out,memberid,userid from t_record where userid in (?...) and accountid_in in (?...) and accountid_out>0"+
            " union select recordid,`day`,amount,ledgerid,0 as accountid_in,accountid_out,memberid,userid from t_record where userid in (?...) and accountid_in>0 and accountid_out in (?...)"+
            ") as v_record", report.getUserids(), report.getAccountids(), report.getAccountids(), report.getUserids(), report.getAccountids(), report.getUserids(), report.getAccountids());
        }else {
            sqlSpec.append(" from t_record");
        }
        sqlSpec.appendIf(report.getUserids().size() > 1, " where userid in (?...)", report.getUserids());
        sqlSpec.appendIf(report.getUserids().size() == 1, " where userid=?", report.getUserids().get(0));
        if(!report.isCountRecords()) sqlSpec.append(" and (accountid_in=0 or accountid_out=0)");
        if (!withTransfer && !report.isCountRecords() && CollectionUtils.isNotEmpty(report.getLedgerids())) {
            if (report.getLedgerids().size() == 1) {
                int ledgerid = report.getLedgerids().get(0);
                if (1 == ledgerid) {
                    sqlSpec.append(" and accountid_out=0");
                } else if (2 == ledgerid) {
                    sqlSpec.append(" and accountid_in=0");
                } else {
                    sqlSpec.append(" and ledgerid=?", ledgerid);
                }
            }
            // sqlSpec.appendIf(report.getLedgerids().size() == 1, " and ledgerid=?", report.getLedgerids().get(0));
            sqlSpec.appendIf(report.getLedgerids().size() > 1, " and ledgerid in (?...)", report.getLedgerids());
        }
        if (CollectionUtils.isNotEmpty(report.getMemberids())) {
            sqlSpec.appendIf(report.getMemberids().size() == 1, " and memberid=?", report.getMemberids().get(0));
            sqlSpec.appendIf(report.getMemberids().size() > 1, " and memberid in (?...)", report.getMemberids());
        }
        if (CollectionUtils.isNotEmpty(report.getAccountids())) {
            sqlSpec.appendIf(report.getAccountids().size() == 1, " and (accountid_in=? or accountid_out=?)",
                    report.getAccountids().get(0), report.getAccountids().get(0));
            sqlSpec.appendIf(report.getAccountids().size() > 1,
                    " and (accountid_in in (?...) or accountid_out in (?...))", report.getAccountids(),
                    report.getAccountids());
        }
        sqlSpec.appendIf(!Utils.isBlank(report.getYear()), " and extract(YEAR from `day`)=?", report.getYear());
        sqlSpec.appendIf(report.getEnd() != null, " and `day` <= ?", report.getEnd());
        sqlSpec.append(" group by `year_month`");
        if ("ledger".equals(report.getGroupBy())) {
            sqlSpec.append(",ledgerid");
        } else if ("member".equals(report.getGroupBy())) {
            sqlSpec.append(",memberid");
        }else if("account".equals(report.getGroupBy())){
            sqlSpec.append(",accountid");
        } else if ("user".equals(report.getGroupBy())) {
            sqlSpec.append(",userid");
        }
        sqlSpec.append(" order by `year_month` desc");
        List<Map> list = sqlUtils.sql(sqlSpec).queryRowList(Map.class);
        return list != null ? list : Collections.emptyList();
    }

    /**
     * <pre>
     * 查询Record流水
     * 场景1：end+limit，定位start，将某day的记录查完，避免翻页时遗漏
     * 场景2：end+start，查询时间范围，翻页时start-1作为end再次传入即可
     * 场景3：userid，查询用户所有（可查两个用户在service层合并报表），可选accountid、withoutTransfer过滤
     * 可选1：accountid，查询账户流水，可用于构建余额变更历史，and (accountid_in=? or accountid_out=?)
     * 可选2：withoutTransfer，是否只查收入支出，不查转账，and (accountid_in=0 or accountid_out=0)
     * 可选3：ledgerid，1查所有收入 2查所有支出 3查所有转账 4+查指定科目
     * 可选4：memberid，查指定成员
     * 可选5：amountMin+amountMax，限定金额范围
     * </pre>
     */
    public List<Record> list(Query query) {
        SqlBuilder sqlSpec = new SqlBuilder();
        sqlSpec.appendIf(!query.isWithRemark(),"select t_record.recordid,`day`,amount,ledgerid,accountid_in,accountid_out,memberid,userid from t_record where userid=?", query.getUserid());
        sqlSpec.appendIf(query.isWithRemark(),"select t_record.recordid,`day`,amount,ledgerid,accountid_in,accountid_out,memberid,userid,t_extra.remark from t_record left join t_extra on t_record.recordid=t_extra.recordid where userid=?", query.getUserid());
        sqlSpec.appendIf(query.getStart() != null && query.getEnd() != null, " and `day` between ? and ?",
                query.getStart(), query.getEnd());
        boolean isLimit = query.getStart() == null && query.getLimit() != null && query.getLimit() > 0;
        if (isLimit) {
            sqlSpec.appendIf(query.getEnd() != null, " and `day`<=?", query.getEnd());
        }
        sqlSpec.appendIf(query.getAccountid() != null && query.getAccountid() > 0,
                " and (accountid_in=? or accountid_out=?)", query.getAccountid(),
                query.getAccountid());
        if (query.getLedgerid() != null && query.getLedgerid() > 0) {
            if (1 == query.getLedgerid()) {
                sqlSpec.append(" and accountid_out=0");
            } else if (2 == query.getLedgerid()) {
                sqlSpec.append(" and accountid_in=0");
            } else if (3 == query.getLedgerid()) {
                sqlSpec.append(" and accountid_in>0 and accountid_out>0");
            } else {
                sqlSpec.append(" and ledgerid=?", query.getLedgerid());
            }
        }
        sqlSpec.appendIf(query.getMemberid() != null && query.getMemberid() > 0, " and memberid=?", query.getMemberid());
        if (!Utils.isBlank(query.getAmountMin()) && !Utils.isBlank(query.getAmountMax())) {
            sqlSpec.append(" and amount between ? and ?", query.getAmountMin(), query.getAmountMax());
        } else if (!Utils.isBlank(query.getAmountMin())) {
            sqlSpec.append(" and amount >= ?", query.getAmountMin());
        } else if (!Utils.isBlank(query.getAmountMax())) {
            sqlSpec.append(" and amount <= ?", query.getAmountMax());
        }
        sqlSpec.appendIf(query.isWithoutTransfer(), " and (accountid_in=0 or accountid_out=0)");
        if (Utils.isNotBlank(query.getRemark())) {
            boolean supportEmoji = DsConfig.supportEmoji(MySqlUtils.dsName());
            if (supportEmoji) {
                sqlSpec.append(" and (instr(t_extra.remark,?)>0 or instr(t_extra.remark,?)>0)",
                        query.getRemark(), ExtraDao.toAlias(query.getRemark()));
            } else {
                sqlSpec.append(" and instr(t_extra.remark,?)>0",
                        ExtraDao.toAlias(query.getRemark()));
            }
        }
        sqlSpec.append(" order by `day` desc,recordid desc");
        if (isLimit) {
            sqlSpec.append(" limit ?", query.getLimit());
        }
        List<Record> list = sqlUtils.sql(sqlSpec).queryRowList(Record.class);
        if (isLimit && list != null && list.size() >= query.getLimit()) {
            query.setStart(list.get(list.size() - 1).getDay());
            if (query.getEnd() == null) {
                query.setEnd(list.get(0).getDay());
            }
            return list(query);
        }
        return list != null ? list : Collections.emptyList();
    }
}
